package com.coursehub.exercise.repository;

import com.coursehub.exercise.entity.Submission;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface SubmissionRepository extends JpaRepository<Submission, Long> {
    
    // 根据作业ID查找所有提交
    List<Submission> findByExerciseId(Long exerciseId);
    
    // 根据学生ID查找所有提交
    List<Submission> findByStudentId(Long studentId);
    
    // 根据作业ID和学生ID查找提交（一个学生对一个作业只能有一个提交）
    Optional<Submission> findByExerciseIdAndStudentId(Long exerciseId, Long studentId);
    
    // 根据提交状态查找提交
    List<Submission> findByStatus(Submission.SubmissionStatus status);
    
    // 根据作业ID和状态查找提交
    List<Submission> findByExerciseIdAndStatus(Long exerciseId, Submission.SubmissionStatus status);
    
    // 根据学生ID和状态查找提交
    List<Submission> findByStudentIdAndStatus(Long studentId, Submission.SubmissionStatus status);
    
    // 根据评分者ID查找提交
    List<Submission> findByGradedBy(Long gradedBy);
    
    // 查找需要评分的提交（已提交但未评分）
    @Query("SELECT s FROM Submission s WHERE s.status = 'SUBMITTED'")
    List<Submission> findSubmissionsNeedingGrading();
    
    // 查找作业的已评分提交
    @Query("SELECT s FROM Submission s WHERE s.exerciseId = :exerciseId AND s.status = 'GRADED'")
    List<Submission> findGradedSubmissionsByExercise(@Param("exerciseId") Long exerciseId);
    
    // 统计作业的提交数量
    long countByExerciseId(Long exerciseId);
    
    // 统计学生的提交数量
    long countByStudentId(Long studentId);
    
    // 统计作业的已评分提交数量
    long countByExerciseIdAndStatus(Long exerciseId, Submission.SubmissionStatus status);
    
    // 检查学生是否已提交作业
    boolean existsByExerciseIdAndStudentId(Long exerciseId, Long studentId);
    
    // 计算作业的平均分
    @Query("SELECT AVG(s.score) FROM Submission s WHERE s.exerciseId = :exerciseId AND s.score IS NOT NULL")
    Double calculateAverageScoreByExercise(@Param("exerciseId") Long exerciseId);
}